#!/bin/bash
########################################################################
# script name:  list_versions.sh
# Used for:
# Author:       Martin Dumont
# Contributors:
# Version:      0.1
#
# (date format: date +'%Y/%m/%d')
#
# Date          Version Contributors    Modifications
# ----------    ------- ------------    -------------
# 2012/07/05    0.1     MDUMONT         initial
########################################################################
usage()
{
cat << STOP

usage: $0 [options]

This script is used for ....

OPTIONS:
   -h               Hosts [all] (only used to update table)
   -q               Query: columns values to query
   -c               Filter columns to display
   -u               Update table with fresh values
   -s               Script output (without separators)

Available columns:
${COLUMNS[@]}

Ex.:
$0 -q ${COLUMNS[0]}=saprmt02
$0 -q ${COLUMNS[2]}=1.8.3p1
$0 -q ${COLUMNS[3]}=5.8,${COLUMNS[8]}=\!11.2.0.1.0
STOP
}
#######################################
# Get parameters
#######################################
command -v /usr/bin/mysql/mysql > /dev/null 2>&1 && MYSQL=/usr/bin/mysql/mysql
command -v /opt/freeware/bin/mysql/mysql > /dev/null 2>&1 && MYSQL=/opt/freeware/bin/mysql/mysql
command -v /usr/bin/mysql > /dev/null 2>&1 && MYSQL=/usr/bin/mysql
if [ -z $MYSQL ]; then echo "Need mysql client installed"; exit 1; fi
if [ -z $QOPTION ]; then QOPTION="-e"; else QOPTION="-Bse"; fi
MYSQL_COMMAND="$MYSQL -hsaplxs01 -uguest -pguest opmgt"
COLUMNS=( $($MYSQL_COMMAND -Bse "SHOW COLUMNS FROM versions" | awk '{print $1}') )
#######################################
while getopts h:q:c:us OPTION
do
	case $OPTION in
		h)
			HOSTS=$(echo $OPTARG | tr '[A-Z]' '[a-z]' | sed 's/,/ /g')
		;;
		q)
			WHERE=( $(echo $OPTARG | tr '[A-Z]' '[a-z]' | sed 's/,/ /g') )
		;;
		c)
			SELECT=$(echo $OPTARG | tr '[A-Z]' '[a-z]')
		;;
		u)
			UPDATE=yes
		;;
		s)
			QOPTION="-Bse"
		;;
		\?)
			usage
			exit 1
		;;
		*)
			usage
			exit 1
		;;
	esac
done
if [ $OPTIND = 1 ]; then usage; exit 1; fi
if [ -z "$HOSTS" ] || [ "$HOSTS" = "localhost" ]; then HOSTS=`uname -n`; fi
if [ "$(whoami)" != "root" ]; then echo "You need to be root (try with sudo)"; exit 1; fi
#######################################
# Variables and environnement
#######################################
SSH_OPTS=" -o ConnectTimeout=5 -o BatchMode=yes -o StrictHostKeyChecking=no -o LogLevel=quiet -T"
BAENV=$(dirname `which $0`)/loadenv.sh
if [ -f $BAENV ]; then . $BAENV; else echo "No env library ($BAENV)"; exit 1; fi
#######################################
# FUNCTIONS
#######################################
function build_query {
	firsttime=y
	for element in ${WHERE[@]}
	do
		e_COLUMN=$(echo $element | cut -d= -f1)
		e_VALUE=$(echo $element | cut -d= -f2)
		for i in ${COLUMNS[@]}
		do
			if [ "$e_COLUMN" = "$i" ]
			then
				if [ "${e_VALUE:0:1}" = "!" ]; then NOT=NOT; e_VALUE=$(echo $e_VALUE | cut -c2-); else NOT=""; fi
				if [ "$e_VALUE" = "all" ] && [ "$i" = "${COLUMNS[0]}" ]; then e_VALUE="%"; fi
				if [ "$firsttime" = "y" ]
				then
					QUERY="WHERE $e_COLUMN $NOT LIKE '%$e_VALUE%' "
					firsttime=n
				else
					QUERY="$QUERY AND $e_COLUMN $NOT LIKE '%$e_VALUE%' "
				fi
			fi
		done
	done
	if [ "$SELECT" = "all" ] || [ -z "$SELECT" ]; then SELECT=\*; fi
}
function update_table {
	while IFS=, read server oslevel sudo ssh lgto tivoli sapkernel sappatch oracle maxdb
	do
		echo $server,$oslevel,$sudo,$ssh,$lgto,$tivoli,$sapkernel,$sappatch,$oracle,$maxdb
		#oslevel=6100-05-07-1140,sudo=1.8.3p1,ssh=OpenSSH_5.8p1,lgto=7.6.3.0,tivoli=06.23.01.00,sapkernel=720,sappatch=72,oracle=11.2.0.1.0,maxdb=NO_EXEC
		QUERY="REPLACE INTO opmgt.versions (server,oslevel,sudo,ssh,lgto,tivoli,sapkernel,sappatch,oracle,maxdb) VALUES "
		QUERY="$QUERY('$server','$oslevel','$sudo','$ssh','$lgto','$tivoli','$sapkernel','$sappatch','$oracle','$maxdb')"
	done < <(read data; echo $data | tr ',' '\n' | cut -d'=' -f2 | xargs | tr ' ' ',')
	sudo -u saunix $MYSQL -v -e "$QUERY"
}
#######################################
#######################################
# MAIN
#######################################
if [ -z $UPDATE ]
then
	build_query
	$MYSQL_COMMAND $QOPTION "SELECT SQL_CACHE DISTINCT $SELECT FROM versions $QUERY ORDER BY ${COLUMNS[0]} ASC "
else
	NEWHOSTLIST=$(checkhost $HOSTS | grep -v Verifying)
	/bin/echo "Updating versions table\n"
	/bin/echo "Populating servers entries:"
	for HOSTNAME in $NEWHOSTLIST
	do
		/bin/echo "$HOSTNAME \c"
		FILE=$LOGDIR/versions/$HOSTNAME.txt
		cat <<-'EOF'|if [ "$HOSTNAME" != "$(uname -n)" ];then /bin/su - saunix -c "ssh $SSH_OPTS $HOSTNAME \"cat|sudo /bin/bash\" 2> /dev/null";else cd /tmp; cat|/bin/sudo /bin/bash 2> /dev/null;fi|update_table
		HOSTNAME=`uname -n`
		##########################
		# OS version
		##########################
		if /bin/uname | /bin/grep -q AIX; then OSLEVEL=`oslevel -s`; else OSLEVEL=`cat /etc/redhat-release | awk '{print $7}'`; fi
		##########################
		# SUDO version
		##########################
		SUDO=`sudo -V | grep version | head -1 | awk '{print $NF}'`
		##########################
		# SSH version
		##########################
		SSH=`ssh -V 2>&1 | cut -d, -f1`
		##########################
		# Tivoli version
		##########################
		if uname | grep -q AIX; then MODULE=ux; else MODULE=lz; fi
		X=/var/opt/IBM/tivoli/bin/cinfo
		if [ -x $X ]; then EXECUTABLE=$X; fi
		X=/opt/IBM/tivoli/bin/cinfo
		if [ -x $X ]; then EXECUTABLE=$X; fi
		if [ -x $EXECUTABLE ]; then TIV_VERSION=$($EXECUTABLE -t $MODULE | grep Lvl | cut -d: -f3); else TIV_VERSION=NO_EXEC; fi
		if [ -z $TIV_VERSION ]; then TIV_VERSION=ERROR; fi
		##########################
		# LGTO version
		##########################
		if [ -x /bin/nsrexecd ]; then FILE=/bin/nsrexecd; fi
		if [ -x /usr/sbin/nsrexecd ]; then FILE=/usr/sbin/nsrexecd; fi
		if [ ! -z $FILE ]; then LGTO=`strings $FILE|grep -i '@(#)' | grep Release | awk '{print $NF}'`; else LGTO=NO_EXEC; fi
		if [ -z $LGTO ]; then LGTO=ERROR; fi
		##########################
		# SAP kernel version
		##########################
		SID=`/usr/local/scripts/list_sys_by_host $HOSTNAME`
		sid=`echo $SID | tr '[A-Z]' '[a-z]'`
		XSID=${sid}adm
		if grep -q "^$XSID" /etc/passwd
		then
			if [ -x /usr/sap/$SID/SYS/exe/run/disp+work ]
			then
				X=$(sudo -i -u $XSID /usr/sap/$SID/SYS/exe/run/disp+work -V | egrep 'kernel release|patch number' | xargs)
				SAP_KERNEL=$(echo $X | cut -d' ' -f3)
				SAP_PATCH=$(echo $X | cut -d' ' -f6)
			else
				SAP_KERNEL=NO_EXEC; SAP_PATCH=NO_EXEC
			fi
		else
			SAP_KERNEL=NO_SID; SAP_PATCH=NO_SID
		fi
		if [ -z $SAP_KERNEL ]; then SAP_KERNEL=ERROR; fi
		if [ -z $SAP_PATCH ]; then SAP_PATCH=ERROR; fi
		##########################
		# Oracle version
		##########################
		XSID=ora${sid}
		if grep -q "^$XSID" /etc/passwd
		then
			if [ -x "$(sudo -i -u $XSID command -v sqlplus)" ]
			then
				ORA_VERSION=$(echo "echo 'quit;' | sqlplus / as sysdba | grep 'Release' | head -1" | sudo -i -u $XSID /bin/bash | cut -d' ' -f3)
			else
				ORA_VERSION=NO_EXEC
			fi
		else
			ORA_VERSION=NO_SID
		fi
		if [ -z $ORA_VERSION ]; then ORA_VERSION=ERROR; fi
		##########################
		# MaxDB version
		##########################
		if [ -x /sapdb/programs/bin/dbmcli ]
		then
			MAXDB_VERSION=$(/sapdb/programs/bin/dbmcli version | grep sapdb | tail -1 | cut -d'"' -f2)
		else
			MAXDB_VERSION=NO_EXEC
		fi
		if [ -z $MAXDB_VERSION ]; then MAXDB_VERSION=ERROR; fi
		/bin/echo "server=$HOSTNAME,oslevel=$OSLEVEL,sudo=$SUDO,ssh=$SSH,lgto=$LGTO,tivoli=$TIV_VERSION,sapkernel=$SAP_KERNEL,sappatch=$SAP_PATCH,oracle=$ORA_VERSION,maxdb=$MAXDB_VERSION"
		EOF
	done
	/bin/echo
fi
#######################################
exit 0

